﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Common;
using Dto;

namespace SqlServerDAL
{
    public class ManageMenuDAL
    {
        private const string Waming = "SqlServerDAL.ManageMenuDAL";
        private const string CompleteSql = "select * from ManageMenu";

        public bool Save(ManageMenuDto dto)
        {
            try
            {
                string sql;
                var parameters = new List<SqlParameter>
                                     {
                                         new SqlParameter("@MenuName", SqlDbType.VarChar) {Value = dto.MenuName},
                                         new SqlParameter("@MenuKey", SqlDbType.VarChar) {Value = dto.MenuKey},
                                         new SqlParameter("@MenuSuperiorID", SqlDbType.VarChar)
                                             {Value = dto.MenuSuperiorID},
                                         new SqlParameter("@MenuPath", SqlDbType.VarChar) {Value = dto.MenuPath},
                                         new SqlParameter("@MenuOrder", SqlDbType.VarChar) {Value = dto.MenuOrder}
                                     };
                if (dto.MenuID == 0)
                {
                    sql =
                        @"insert into ManageMenu(MenuName,MenuKey,MenuSuperiorID,MenuPath,MenuOrder) values(@MenuName,@MenuKey,@MenuSuperiorID,
                        @MenuPath,@MenuOrder)";
                }
                else
                {
                    sql =
                        @"update ManageMenu set MenuName=@MenuName,MenuKey=@MenuKey,MenuSuperiorID=@MenuSuperiorID,MenuPath=@MenuPath,
                        MenuOrder=@MenuOrder where MenuID=@MenuID";
                    parameters.Add(new SqlParameter("@MenuID", SqlDbType.Int) { Value = dto.MenuID });
                }
                SqlHelper.ExecuteNonQuery(SqlHelper.connStr, CommandType.Text, sql, parameters.ToArray());
                return true;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".Save(ManageMenuDto dto)", ex);
                return false;
            }
        }

        public bool Delete(int id)
        {
            try
            {
                const string sql = "delete from ManageMenu where MenuID=@MenuID";
                var param = new SqlParameter("@MenuID", SqlDbType.Int) { Value = id };
                SqlHelper.ExecuteNonQuery(SqlHelper.connStr, CommandType.Text, sql, param);
                return true;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".Delete(int id)", ex);
                return false;
            }
        }

        public List<Hashtable> FindAll()
        {
            try
            {
                List<Hashtable> hts;
                using (var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, CompleteSql))
                {
                    hts = new List<Hashtable>();
                    while (red.Read())
                    {
                        var dto = SqlHelper.FillHashtable(red);
                        hts.Add(dto);
                    }
                }
                return hts;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".FindAll()", ex);
                return null;
            }
        }

        public List<Hashtable> GetList(int superiorId)
        {
            try
            {
                List<Hashtable> hts;
                const string sql = CompleteSql + " where MenuSuperiorID=@MenuSuperiorID order by MenuOrder asc";
                var paraList = new List<SqlParameter> { new SqlParameter("@MenuSuperiorID", SqlDbType.Int) { Value = superiorId } };
                using (var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql,
                                                         paraList.ToArray()))
                {
                    hts = new List<Hashtable>();
                    while (red.Read())
                    {
                        var dto = SqlHelper.FillHashtable(red);
                        hts.Add(dto);
                    }
                }
                return hts;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".GetList(int superiorId)", ex);
                return null;
            }
        }

        public Hashtable FindFirstBySuperiorID(int superiorId)
        {
            try
            {
                Hashtable ht = null;
                const string sql = CompleteSql + " where MenuSuperiorID=@MenuSuperiorID order by MenuOrder asc";
                var paraList = new List<SqlParameter> { new SqlParameter("@MenuSuperiorID", SqlDbType.Int) { Value = superiorId } };
                using (var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql,
                                                         paraList.ToArray()))
                {
                    if (red.Read())
                    {
                        ht = SqlHelper.FillHashtable(red);
                    }
                }
                return ht;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".FindFirstBySuperiorID(int superiorId)", ex);
                return null;
            }
        }

        public Hashtable FindById(int id)
        {
            Hashtable ht = null;
            const string sql = CompleteSql + " where MenuID=@MenuID";
            var paraList = new List<SqlParameter> { new SqlParameter("@MenuID", SqlDbType.Int) { Value = id } };
            using (var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql,
                                                     paraList.ToArray()))
            {
                if (red.Read())
                {
                    ht = SqlHelper.FillHashtable(red);
                }
            }
            return ht;
        }
    }
}
